﻿
-- ================================================
-- Remarks: Not part of standard Stored Proc
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Ivy_Sys_RoleActivity_GetAllByRoleKeyActivityCode')
	BEGIN
		DROP  Procedure  Ivy_Sys_RoleActivity_GetAllByRoleKeyActivityCode
	END

GO

CREATE PROCEDURE [Ivy_Sys_RoleActivity_GetAllByRoleKeyActivityCode] 
-- =============================================
-- Author:		<Ram pravesh kr>
-- Create date: <09/06/2016>
-- Description:	<Ivy_Sys_RoleActivity_GetAllByRoleKeyActivityCode>
-- =============================================
-- Add the parameters for the stored procedure here
	(
				@RoleKey int,
				@ActivityCode varchar(50)
				
	)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT OFF;

	SELECT    [Sys_ActivityMaster].[ActivityKey], 
	          [Sys_ActivityMaster].[ActivityCode], 
			  [Sys_ActivityMaster].[ActivityName], 
			  [Sys_ActivityMaster].[ActivityDescription], 
			  [Sys_ActivityTypeMaster].[ActivityTypeCode], 
              [Sys_ActivityTypeMaster].[ActivityTypeName]
    FROM      [Sys_ActivityMaster] LEFT OUTER JOIN [Sys_ActivityTypeMaster] 
	ON        [Sys_ActivityMaster].[ActivityTypeKey] = [Sys_ActivityTypeMaster].[ActivityTypeKey]
    WHERE     (
	            [Sys_ActivityMaster].[ActivityKey] NOT IN
              (
    SELECT     [Sys_ActivityMaster].[ActivityKey]
    FROM       [Sys_ActivityMaster] RIGHT OUTER JOIN [Sys_RoleActivity] 
	ON         [Sys_ActivityMaster].[ActivityKey] = [Sys_RoleActivity].[RoleActivityKey]
    WHERE      (
	             [Sys_RoleActivity].[RoleKey] = @RoleKey))
			   ) AND 
			   (
			   [Sys_ActivityMaster].[ActivityCode] = @ActivityCode
			   )
END
GO
